package com.darren.euc.dao.impl;


import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Repository;

import com.darren.euc.dao.ClientDao;
import com.darren.euc.entity.Client;


@Repository
public class ClientDaoImpl extends BaseDaoImpl<Client, Integer> implements ClientDao {
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Client> getAll() {
		Session session = sessionFactory.getCurrentSession();	
		List<Client> clients = session.createQuery("from Client ").list();
		return clients;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Client> getActiveclient() {
		Session session = sessionFactory.getCurrentSession();
		String queryString = "select distinct client.*  from euc_client client  LEFT JOIN euc_orders orders  on client.id = orders.client_id  LEFT JOIN euc_contracts contract on contract.order_id = orders.id  where EXISTS(select 1 from euc_contract_product product	where product.contract_id = contract.id  and datediff(product.rent_start,CURDATE()) <=0	and datediff(product.rent_end,CURDATE()) >=0 ) ;";
		Query query = session.createSQLQuery(queryString).addEntity(Client.class);
		List<Client> clients = query.list();
		return clients;
	}
	
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Client> getMemberClient() {
		Session session = sessionFactory.getCurrentSession();
		String queryString = "SELECT DISTINCT	client.* FROM	euc_client client INNER JOIN euc_orders orders ON client.id = orders.client_id  ";
		Query query = session.createSQLQuery(queryString).addEntity(Client.class);
		List<Client> clients = query.list();
		return clients;
	}

	
	@SuppressWarnings("unchecked")
	@Override
	public List<Client> getClientForMember() {
		Session session = sessionFactory.getCurrentSession();
		String queryString = "SELECT DISTINCT	client.*  FROM	euc_client client  LEFT JOIN euc_orders orders ON client.id = orders.client_id;";
		Query query = session.createSQLQuery(queryString).addEntity(Client.class);
		List<Client> clients = query.list();
		return clients;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<Client> findByIds(List<Integer> ids) {
		Session session = sessionFactory.getCurrentSession();
		Query query  = session.createQuery("from Client where id in (:ids)");
        query.setParameterList("ids", ids);
		return query.list();
	}

	@Override
	public List<Client> getClientByRoomId(Integer roomId){
		Session session = sessionFactory.getCurrentSession();
		String sql = "SELECT client.* FROM euc_contract_product as product INNER JOIN euc_contracts as contracts on contracts.id = product.contract_id INNER JOIN euc_orders as orders on orders.id = contracts.order_id INNER JOIN euc_client as client on client.id  = orders.client_id where product.room_id = ? ORDER BY product.rent_start asc;";
		Query query  = session.createSQLQuery(sql).addEntity(Client.class);
		query.setParameter(0, roomId);	
		return query.list();
	}
	
	private static String getAllRentSeatsSQL = "SELECT SUM(product.seat_count) as seat_count "+
												"	FROM euc_contract_product as product "+
												"	INNER JOIN euc_contracts as contract on product.contract_id = contract.id "+
												"	INNER JOIN euc_orders as roomorder on roomorder.id = contract.order_id "+
												"	WHERE roomorder.client_id = ? "+
												"	AND contract.contract_end_time >= CURDATE();"; 
	
	@Override
	public BigInteger getAllRentSeats(Integer clientId){
		Session session = sessionFactory.getCurrentSession();	
		Query query  = session.createSQLQuery(getAllRentSeatsSQL);
		query.setParameter(0, clientId);
		//BigInteger count = (BigInteger) query.uniqueResult();
		BigDecimal count = (BigDecimal) query.uniqueResult();
		return  count.toBigInteger();
	}
}